<?php

namespace app\student\model;

use think\Db;
use think\Exception;

class studentModel
{

    //里面有模糊查询的判断，应该连同科目的老师一起查出来
    public static function selectStudentModel(array $where=null)
    {
            //select s.*,cls.name as cls_name, GROUP_CONCAT(t.name,c.name) as t_c_name from s_student s
            //LEFT JOIN s_report  r on s.id = r.s_id
            //LEFT JOIN s_classes cls on cls.id = r.cls_id
            //LEFT JOIN s_course c on c.id = r.c_id
            //LEFT JOIN s_teacher t on t.id = r.t_id
            //GROUP BY s.id
        $start = Db::table('s_student')->alias('s')
            ->field('s.*,cls.name as cls_name,GROUP_CONCAT(t.name,"-",c.name) as t_c_name')
            ->leftJoin('s_report r','s.id = r.s_id')
            ->leftJoin('s_classes cls','cls.id = r.cls_id')
            ->leftJoin('s_course c','c.id = r.c_id')
            ->leftJoin('s_teacher t','t.id = r.t_id');

        //查询科目
        $c_name = null;
        if((isset($where['c_name'])) && $where)
        {
            $c_name = $where['c_name'];
        }

         if($c_name)
         {
               $start->where('c.name','like',"%".$c_name."%");
         }


         $result = $start->group('s.id')->select();
        return $result;
    }


    //查询出班级的名字和id
    public static function selectClasses()
    {
        $result = Db::table('s_classes')->select();
        return $result;
    }

    //查询出科目的名字和id
    public static function selectCourse()
    {
        $result = Db::table('s_course')->select();
        return $result;
    }

    //查询report 表里面的数据
    public static function selectReport(array $where)
    {
        $result = Db::table('s_report')->where($where)->select();
        return $result;
    }






    //当添加学生时候应该连同成绩表里面的学生一样添加
    public static function insertStudentModel(array $insert)
    {
        //当添加学生表的时候，同样需要添加成绩表
        //但是这时候问题来了，我们科目中有可能一个科目有几个老师，所以这时候我需要通过已知的科目id来求出老师id，并对成绩表进行添加

        //这里抽取出成绩表相关的科目id,
        // $course['c_id']这样写的话造成是带键的数组,如果循环的话造成value的值是按照键去找值的
        //可是我们通过c_id 去判断循环的是 数字，而不是一个键名为c_id 的数组
        if(isset($insert['c_id']))
        {
            $course = $insert['c_id'];
            unset($insert['c_id']);
        }

       // var_dump($insert,$course);
        //开启事务
        Db::startTrans();
        try {
            $start  = Db::table('s_student')->insert($insert);
            //这里的id，是为了之后添加成绩表所以提前获取
            $insert['id'] = Db::table('s_student')->getLastInsID();
            if(empty($start))
            {
                //回滚
                Db::rollback();
            }else{
                //通过科目id要求出老师表的id
                //select tea.c_id,tea.t_id from s_course c
                //LEFT JOIN s_teaching tea on c.id = tea.c_id
                //LEFT JOIN s_teacher t on t.id = tea.t_id
                //where c_id = 2
                foreach ($course as $key=>$value)
                {

                    //var_dump($val);die;
                    $teacher = Db::table('s_course')->alias('c')->field('tea.c_id,tea.t_id')
                                    ->leftJoin('s_teaching tea','c.id = tea.c_id')
                                    ->leftJoin('s_teacher t','t.id = tea.t_id')
                                    ->where('c_id',$value)->select();
                    //var_dump($insert,$teacher);die;
                    //echo Db::getLastSql();die;
                    foreach ($teacher as $k=>$val)
                    {
                        //科目id，老师id，学生id，班级id
                        //这里将之前早已经获取好的学生id和班级id传过来，而学生id和班级id不用改变，所以提前获取
                        $val['s_id'] = $insert['id'];
                        $val['cls_id'] = $insert['cls_id'];
                        //var_dump($val);die;
                        $report = Db::table('s_report')->insert($val);
                    }
                }
                //防止没有对成绩表进行添加，其实也防止了学生没有选科，这里表示学生必须选择一科，添加SQL执行影响条数是0，就会回滚导致添加失败
                if(empty($report))
                {
                    //回滚
                    Db::rollback();
                }else{
                    //提交
                    Db::commit();
                    return $start;
                }

            }

        }catch (Exception $exception) {
            //回滚
            Db::rollback();
        }
        
    }


    //当删除学生时候应该连成绩表里面的学生信息一同删除
    public static function deleteStudentModel(array $where)
    {
        //var_dump($where);die;
        //我们需要先删除学生表里面的数据，然后在删除成绩表里面的数据
        //开启事务
        Db::startTrans();
        try {
            $start = Db::table('s_student')->where($where)->delete();
            if(empty($start))
            {
                //回滚
                Db::rollback();
            }else{
                $where['s_id']= $where['id'];
                unset($where['id']);
                //var_dump($where);die;
                $result = Db::table('s_report')->where($where)->delete();
                if(empty($result))
                {
                    //回滚
                    Db::rollback();
                }else{
                    //提交事务
                    Db::commit();
                    return $result;
                }

            }


        }catch(Exception $exception){
            //回滚
            Db::rollback();
        }

        return null;
    }



    //查询出学生原先状态
    public static function GetStudentModel(array $where)
    {
        $result = Db::table('s_student')->get($where);
        return $result;
    }

    //当修改学生时候应该连成绩表里面的学生信息一同修改
    public static function updateStudent(array $where)
    {
        //我们修改学生表之后同样的，我们需要修改成绩表里面的数据
        //var_dump($where);die;
        //判断是否学生修改科目导致没有选择一科
        if(isset($where['c_name']))
        {
            $report = $where['c_name'];
            unset($where['c_name']);
        }else{
            return null;
        }
        //开启事务
        Db::startTrans();
        try {

            $start = Db::table('s_student')->where('id',$where['id'])->update($where);
            //var_dump($start);die;
            //这里不需要对$start进行判断因为，防止学生只修改了科目
                //我们对成绩表进行修改则必须先删除原有的成绩表数据
                //var_dump(1);die;
                $delete = Db::table('s_report')->where('s_id',$where['id'])->delete();
                if(empty($delete))
                {
                    //回滚
                    Db::rollback();
                }else{
                    //删除成绩表数据之后，在对成绩表添加新的数据
                        foreach ($report as $value)
                        {
                            //select tea.c_id,tea.t_id from s_course c
                            //LEFT JOIN s_teaching tea on c.id = tea.c_id
                            //LEFT JOIN s_teacher t on t.id = tea.t_id
                            //where c_id = 2
                            //这里我们要通过科目id查出老师的id,同时也涵盖者科目id
                            $teacher = Db::table('s_course')->alias('c')->field('tea.c_id,tea.t_id')
                                                ->leftJoin('s_teaching tea','c.id = tea.c_id')
                                                ->leftJoin('s_teacher t','t.id = tea.t_id')
                                                ->where('c_id',$value)->select();

                            //科目id，老师id，学生id,班级id
                            foreach ($teacher as $val)
                            {
                                //学生id
                                $val['s_id'] = $where['id'];
                                //班级id
                                $val['cls_id'] = $where['cls_id'];
                                //var_dump($val);die;
                                $result = Db::table('s_report')->insert($val);
                            }

                        }

                        //多加一次判断防止学生没有选科
                        if($result)
                        {
                            //提交
                            Db::commit();
                            return $result;
                        }else{
                            //回滚
                            Db::rollback();
                        }
                }
        }catch (Exception $exception){
            //回滚
            Db::rollback();
        }

    }

}